package com.deng.etl.excel.impl;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.io.PrintWriter;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import javax.servlet.ServletException;
import javax.servlet.ServletInputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileUploadException;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.WritableWorkbook;

import com.deng.common.vo.OrganizationVo;
import com.deng.etl.excel.IExcelRule;
import com.deng.maintenance.dao.impl.KPIManager;
import com.deng.maintenance.vo.KpiDictItemVsOrganizationVo;
import com.deng.maintenance.vo.KpiValueVo;
import com.deng.util.DataFormater;
import com.deng.util.DateTools;
import com.deng.util.GlobalProperties;
import com.deng.util.MsgReader;
import com.deng.util.PropertiesReader;

public class MenjizhenliangServlet extends HttpServlet {

	private final static String P_FILE = "Menjizhenliang.properties";
	private final static String N_FILE_NAME="门急诊量";	
	private final static String GUIKOU_ID = "63";
	private String uploadPath = new MsgReader().getPt("EXCEL_FILE_URL");
	// 限制文件的上传大小
	private int maxPostSize = 100 * 1024 * 1024;	
//	private List<KpiDictItemVsOrganizationVo> tmpL ;	
	private List<OrganizationVo> orgList ;
	private Connection conn ;//= getConnection();
	private List<MappingVo> pList = new ArrayList<MappingVo>();
	
	private void getConnection() throws Exception{
		Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		conn = DriverManager.getConnection(
    			new MsgReader().getPt("CAL_BONUSE_DB"),
    			new MsgReader().getPt("CAL_BONUSE_USER_NAME"),
    			new MsgReader().getPt("CAL_BONUSE_PW"));
		conn.setAutoCommit(false);
//    	return conn;
	}
	private void initOrganization() throws Exception{
		orgList = new ArrayList<OrganizationVo>();
		ResultSet rs = queryData(conn, "SELECT * from t_sys_stat_department");
		while(rs.next()){
			OrganizationVo orgVo = new OrganizationVo();
			orgVo.id = rs.getInt("id");
			orgVo.name = rs.getString("name");
			orgVo.duty_id = rs.getInt("duty_id");
			orgList.add(orgVo);
		}
	}
	private int getKaoheId(String orgName,String fanwei){
		if(fanwei != null && (fanwei.equals("主任") || fanwei.equals("科主任"))){
			for(OrganizationVo orgVo:orgList){
				if(orgVo.name.equals(orgName) && 2 == orgVo.duty_id){
					return orgVo.id;
				}
			}
		}else{
			for(OrganizationVo orgVo:orgList){
				if(orgVo.name.equals(orgName) && 1 == orgVo.duty_id){
					return orgVo.id;
				}
			}
		}
		return 0;
	}
	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException {
		// TODO Auto-generated method stub
		doPost(req, resp);
	}
	protected ResultSet queryData(Connection conn,String sql){
		System.out.println("::::queryData:::::   "+sql);
		PreparedStatement pstmt = null;
		try{
			pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			return rs;
		}catch(Exception ex){
			ex.printStackTrace();	
		}
		return null;
	}
	protected int updateData(Connection conn,String sql){
		System.out.println("::::updateData:::::   "+sql);		
		PreparedStatement pstmt = null;
		try{
			pstmt = conn.prepareStatement(sql);
			return pstmt.executeUpdate();
		}catch(Exception ex){
			ex.printStackTrace();	
		}
		return 0;
	}
	
	private KpiValueVo getKVV(Integer kpi_id,Integer c_kpi_id,Integer guikou_id,Integer kaohe_id) throws Exception{
		ResultSet rs;
		KpiValueVo vo = new KpiValueVo();
		if(c_kpi_id != 0){
			rs = queryData(conn, "select * from KPI_DETAIL_VS_ORGANIZATION where parent_id = "+kpi_id +" and kpi_detail_id = "+c_kpi_id+" and kaohe_id = "+kaohe_id +" and guikou_id = "+guikou_id);
			if(rs.next()){
				System.out.println("找到该条子数据");
				vo.c_formula = rs.getString("formula");
				vo.detail_type = 2;//子数据 detail_type一定等于2
				vo.c_id = 1982;
				return vo;
			}			
		}else{
			rs = queryData(conn, "select * from KPI_VS_ORGANIZATION where kpi_id = "+kpi_id +" and kaohe_id = "+kaohe_id +" and guikou_id = "+guikou_id);
			if(rs.next()){
				System.out.println("找到该条父数据");
				vo.formula = rs.getString("formula");
				vo.detail_type = rs.getInt("detail_type");
				return vo;
			}
		}
		return null;
	}
	
	//以每一条作为事务单独保存。  子的数据和父的数据无关
	private void insertKPIDetailValue(String y,String m,String kpi_id,String c_kpi_id,String guikou_id,
			String kaohe_id,String value,String desc){
		try{
			String sql = "SELECT * from KPI_DETAIL_VALUE"+
				" where year="+y+" and month="+m+
				" and kpi_detail_id = "+c_kpi_id+
				" and parent_id = "+kpi_id+
				" and kaohe_id = "+kaohe_id+
				" and guikou_id = "+guikou_id;
			ResultSet rs = queryData(conn, sql);
			KpiValueVo kvv = getKVV(Integer.valueOf(kpi_id),Integer.valueOf(c_kpi_id),
					Integer.valueOf(guikou_id),Integer.valueOf(kaohe_id));
			if(kvv == null){
				System.out.println("不存在科室和项目的对应关系");
				return;
			}
			kvv.c_tmp_value = value;//子数据要的是c_tmp_value
			kvv = new KPIManager().valueToScore(kvv, conn);				
			String score = kvv.c_score;			
			if(rs.next()){
				Integer status = rs.getInt("valid");
				if(status == null || status == 0 || status == 5 || status == 2){
					System.out.println("可变更");
					Integer id = rs.getInt("id");
					sql = "UPDATE KPI_DETAIL_VALUE" +
							" set value= "+value+"," +
							"score="+score+", " +
							"create_time='"+DateTools.dtsWithTime(new Date())+"'" +
							" where id="+id;
					updateData(conn, sql);
				}else{
					System.out.println("不可更改状态");
				}
				
			}else{
				System.out.println("插入新的子数据");
				sql = "insert into  KPI_DETAIL_VALUE" +
					"	(year,month," +
					"	parent_id,kpi_detail_id," +
					"   kaohe_id,guikou_id," +
					"	value,score,create_time,valid)" +
					"	values("+y+","+m+","+
						kpi_id+","+c_kpi_id+","+
						kaohe_id+","+guikou_id+"," +
						value+","+score+",'"+DateTools.dtsWithTime(new Date())+"',5)";
				updateData(conn, sql);
			}   
			conn.commit();	
		}catch(Exception ex){
			ex.printStackTrace();
			try{
				if(conn != null){
					conn.rollback();//回滚。
				}
			}catch(Exception ex2){
				ex2.printStackTrace();
			}
		}finally{
			try{
//				if(conn != null){
//					conn.setAutoCommit(true);
//				}
			}catch(Exception ex2){
				ex2.printStackTrace();
			}
		}
	}
	
	//以每一条作为事务单独保存。
	private void insertKPIValue(String y,String m,String kpi_id,String guikou_id,
				String kaohe_id,String value,String desc) throws Exception{
		try{
			String sql = "select * from KPI_VALUE " +
					"	where year = "+y+
					"	and month = "+m +
					"	and kpi_id = "+kpi_id+
					" 	and kaohe_id = " +kaohe_id+
					"	and guikou_id = "+guikou_id;
			ResultSet rs = queryData(conn, sql);
			KpiValueVo kvv = getKVV(Integer.valueOf(kpi_id),0,
					Integer.valueOf(guikou_id),Integer.valueOf(kaohe_id));
			if(kvv == null){
				System.out.println("不存在科室和项目的对应关系");
				return;
			}
			kvv.tmp_value = value;
			kvv = new KPIManager().valueToScore(kvv, conn);				
			String score = kvv.score;
			
			if(rs.next()){
				Integer status = rs.getInt("valid");
				if(status == null || status == 0 || status == 5 || status == 2){
					System.out.println("可变更");
					int id = rs.getInt("id");				
					if(score != null){//说明这个科室要导入
						sql = "update KPI_VALUE set score = "+score+","+
							"	value = "+value+
							"	where id = "+id;					
					}	
				}else{
					System.out.println("不可更改状态");
				}
							
			}else{
				System.out.println("插入新的父数据");
				sql = "insert into  KPI_VALUE" +
				"	(year,month," +
				"	kpi_id,kaohe_id,guikou_id," +
				"	value,score,create_time,valid)" +
				"	values("+y+","+m+","+
					kpi_id+","+kaohe_id+","+guikou_id+"," +
					value+","+score+",'"+DateTools.dtsWithTime(new Date())+"',5)";
			}
			updateData(conn, sql);
			conn.commit();	
		}catch(Exception ex){
			ex.printStackTrace();
			try{
				if(conn != null){
					conn.rollback();//回滚。
				}
			}catch(Exception ex2){
				ex2.printStackTrace();
			}
		}finally{
			try{
//				if(conn != null){
//					conn.setAutoCommit(true);
//				}
			}catch(Exception ex2){
				ex2.printStackTrace();
			}
		}
			
	}
	
	/**
	老的格式：
	格式：Sheet1
	列：    科室名称（中文）， 考核范围，得分， 描述，
	如：  外科，   科主任 （如不填默认是科室）， 92.13，  成绩比较好，  
	 *
	 * @param fName
	 * @param y
	 * @param m
	 * @param guikou_id
	 * @param kpi_id
	 * @param c_kpi_id
	 */
	private void managerFile1(String fName,int y ,int m){
		try{
			File f = new File(fName);
			int time = 0;
			while(!f.canRead()){
				if(time > 10){
					return;
				}
				time ++;
				Thread.sleep(1000);
				
			}
			Workbook book = Workbook.getWorkbook(new File(fName));
			Sheet sh = book.getSheet(0);//.createSheet("第一页", 0);
			Map<String,RowVo> bm = new HashMap<String,RowVo>();
			//格式：Sheet1
			//列：    科室，XX， XX， XX，使用率
			int rownum = sh.getRows();
			for(int i=1;i<rownum;i++){//第二行开始。
				Cell cOrgName = sh.getCell(0,i);//EXCEL科室名称
				if(cOrgName != null 
						&& cOrgName.getContents() != null 
						&& !"".equals(cOrgName.getContents().trim())){
					
					Cell cPoint = sh.getCell(8,i);//得分
					if(cPoint != null 
							&& cPoint.getContents()!=null 
							&& !"".equals(cPoint.getContents().trim())
							){
						try{
							Float fv = new Float(cPoint.getContents().trim().replace("%", "").replace("％", ""));
							System.out.println("<<<<<<<<<<<::  "+fv);
							
							//遍历Excel，得到科室名字
							//通过科室名字得到对应插入科室（考核科室）的ID							
							String kaoheId = getValue(cOrgName.getContents().trim());
							if(kaoheId == null){
								continue;
							}
							RowVo bv = bm.get(kaoheId);
							if(bv != null){//说明存在，要合并
								bv.count ++;
								bv.value += fv;
								bm.put(kaoheId, bv);
							}else{//不存在，全新插入								
								RowVo vo = new RowVo(kaoheId,fv);
								bm.put(kaoheId, vo);
							}							
						}catch(Exception ex){
							System.out.println(cPoint.getContents().trim()+":非数字，略过");
//							ex.printStackTrace();
						}
					}
				}					
			}	
			for(int i = 0;i< bm.size();i++){
				String kaoheId = bm.keySet().toArray()[i]+"";
				String value = ((RowVo)bm.values().toArray()[i]).value+"";
				System.out.println("汇总："+kaoheId+"   "+value);
				insertKPIValue(y+"",m+"","116",GUIKOU_ID,
							kaoheId,value,"数据导入");
				
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}
		
	}
	
	private void initPrt(){
		Properties prt = new PropertiesReader().getAll(P_FILE);
		System.out.println(prt);
		MappingVo vo;
		for(int i = 0;i<prt.size();i++){
			String key = prt.keySet().toArray()[i]+"";
			String value = prt.values().toArray()[i]+"";
			System.out.println(key+"   =    "+value);
			vo = new MappingVo(key,value);
			pList.add(vo);
		}
	}
	
	private String getKey(String excelOrgName){
		if(excelOrgName == null)return null;
		for(MappingVo mv:pList){
			if(mv.value.equals(excelOrgName.trim())){
				return mv.key;
			}
		}
		return null;
	}
	private String getValue(String key){
		System.out.println("******"+key);
		if(key == null)return null;
		for(MappingVo mv:pList){
			if(mv.key.equals(key.trim())){
				System.out.println(": "+mv.value);
				return mv.value;
			}
		}
		return null;
	}
	@Override
	protected void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		try{
			getConnection();
			initOrganization();
			initPrt();//key = 插入的id   value ＝ excel上的值
			
//			String fileName = request.getParameter("fileName");
			String guikou_id = "";//request.getParameter("guikou_id");//归口科室id
			String y = request.getParameter("y");
			String m = request.getParameter("m");
//			
			String newFileName="["+y+"年-"+m+"月] "+N_FILE_NAME+".xls";
			System.out.println(newFileName);
			
	        response.setContentType("text/html;charset=UTF-8");
//	        PrintWriter out = response.getWriter();

	        //保存文件到服务器中

	        DiskFileItemFactory factory = new DiskFileItemFactory();
	        factory.setSizeThreshold(4096);
	        ServletFileUpload upload = new ServletFileUpload(factory);
	        upload.setSizeMax(maxPostSize);
	        try {
	            List fileItems = upload.parseRequest(request);
	            Iterator iter = fileItems.iterator();
	            while (iter.hasNext()) {
	                FileItem item = (FileItem) iter.next();
	                if (!item.isFormField()) {
//	                    String name = item.getName();
//	                    System.out.println(name);
	                    try {
		        			File f = new File(uploadPath);
		        			if(!f.exists()){
		        				f.createNewFile();
		        			}
		        			File findF = new File(uploadPath + newFileName);
		        			if(findF.exists()){
		        				findF.delete();
		        			}
	                        item.write(findF);
	                       // SaveFile s = new SaveFile();
	                        managerFile1(uploadPath + newFileName,
	                        		Integer.valueOf(y),
	                        		Integer.valueOf(m));
	                       // s.saveFile(name);

	                    } catch (Exception e) {
	                        e.printStackTrace();
	                    }
	                }
	            }
	        } catch (FileUploadException e) {
	            e.printStackTrace();
	            System.out.println(e.getMessage() + "结束");
	        }			
			
			
			
//			ServletInputStream is = req.getInputStream(); 
//			saveFile(is,"d:\\a.xls");
//			
//			
//			byte[] junk = new byte[1024];     
//			for(int i = 0; i < 8; i++){     
//				is.readLine(junk, 0,junk.length);
//				System.out.println(junk);
////				fi.write(junk);
//			}
//			
//			jxl.Workbook wb=jxl.Workbook.getWorkbook(is);  
//			



////			FileOutputStream fi = new FileOutputStream(f);
////			BufferedWriter bw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(new File(newFileName))));
//			
//			
//			
		}catch(Exception ex){
			ex.printStackTrace();
		}
		
		
//		String fileName = req.getParameter("fileName");
//		String id = req.getParameter("id");
//		String stat_time = req.getParameter("stat_time");
//		String type_name = req.getParameter("type_name");
//		String class_name = req.getParameter("class_name");
//		String update = req.getParameter("update");
//		String fileLocal = GlobalProperties._EXCEL_LOCAL+fileName+"_"+(new Date().getTime())+".xls";
//		if(!new File(GlobalProperties._EXCEL_LOCAL).exists()){
//			new File(GlobalProperties._EXCEL_LOCAL).mkdirs();
//		}
//		File f = new File(fileLocal);
//		
//		FileOutputStream  fos = new FileOutputStream(f);
//		byte[] b = new byte[1024];
//		while (is.read(b) != -1) {
//		   fos.write(b);
//		}
//		fos.close();
//		is.close();
//		
//		try{
//			System.out.println(GlobalProperties._EXCEL_IMPORT_CLASS+class_name);
//			Class cl = Class.forName(GlobalProperties._EXCEL_IMPORT_CLASS+class_name);
//			IExcelRule ei = (IExcelRule) cl.newInstance();
//			ei.importData(fileLocal, stat_time, type_name, id,update);
//		}catch(Exception ex){
//			ex.printStackTrace();
//		}
	}

}

